import pymysql  #导入数据库模块
from tkinter import *  #导入tkinter模块
from tkinter import messagebox  #导入弹窗模块


# 连接数据库studentinfo
def LinkMysql():
    con = pymysql.connect(host="localhost",  #主机地址
                          user="root",  #用户
                          password="123456",  #密码
                          database="studentinfo")  #数据库
    #使用 cursor() 方法创建一个游标对象 cursor
    cur = con.cursor()
    return con, cur  #返回连接对象和游标对象


#设置窗口信息
def Windows():
    global root  #定义全局变量root
    root = Tk()
    # 禁止最大化按钮（只显示最小化按钮和关闭按钮）
    root.resizable(False, False)
    root.title("学生信息管理系统")  #标题
    root.geometry("600x600")  #大小


#设置菜单栏菜单
def menu():
    menubar = Menu(root)  #设置菜单栏
    filemenu = Menu(menubar)  #创建菜单栏
    menubar.add_cascade(label="菜单", menu=filemenu)  #添加主菜单栏选项
    filemenu.add_command(label="增加", command=InsertStu)  #添加菜单栏选项
    filemenu.add_command(label="删除", command=DeleteStu)  #添加菜单栏选项
    filemenu.add_command(label="修改", command=ChangeStu)  #添加菜单栏选项
    filemenu.add_command(label="查询", command=SelectStu)  #添加菜单栏选项
    filemenu.add_separator()  #添加分隔线
    filemenu.add_command(label="退出", command=root.destroy)
    #显示菜单
    root.config(menu=menubar)


#主页按钮
def MainButton():
    #录入学生信息按钮,设置字体和背景颜色
    BtuInsertStu = Button(root, text="录入学生信息", font=("微软雅黑 -20"), bg="gray", command=InsertStu)
    BtuInsertStu.place(x=200, y=150, height=40, width=200)
    #删除学生信息按钮,设置字体和背景颜色
    BtuDeleteStu = Button(root, text="删除学生信息", font=("微软雅黑 -20"), bg="gray", command=DeleteStu)
    BtuDeleteStu.place(x=200, y=250, height=40, width=200)
    #修改学生信息按钮,设置字体和背景颜色
    BtuChangeStu = Button(root, text="修改学生信息", font=("微软雅黑 -20"), bg="gray", command=ChangeStu)
    BtuChangeStu.place(x=200, y=350, height=40, width=200)
    #查询学生信息按钮,设置字体和背景颜色
    BtuSelectStu = Button(root, text="查询学生信息", font=("微软雅黑 -20"), bg="gray", command=SelectStu)
    BtuSelectStu.place(x=200, y=450, height=40, width=200)
    root.mainloop()


#增加学生信息
def InsertStu():  #录入学生信息
    root1 = Tk()
    root1.title("录入学生信息")
    root1.geometry("600x600")  #大小
    #创建关联字符变量
    IdVar = StringVar(root1, value="")  #学号
    NameVar = StringVar(root1, value="")  #姓名
    GenderVar = StringVar(root1, value="")  #性别
    TelVar = StringVar(root1, value="")  #电话
    AgeVar = StringVar(root1, value="")  #年龄

    #创建标签组件
    IdLabel = Label(root1, text="学号：", font=("微软雅黑 -20"))
    IdLabel.place(x=30, y=60, height=40, width=80)
    NameLabel = Label(root1, text="姓名：", font=("微软雅黑 -20"))
    NameLabel.place(x=30, y=110, height=40, width=80)
    GenderLabel = Label(root1, text="性别：", font=("微软雅黑 -20"))
    GenderLabel.place(x=30, y=160, height=40, width=80)
    TelLabel = Label(root1, text="电话：", font=("微软雅黑 -20"))
    TelLabel.place(x=30, y=210, height=40, width=80)
    AgeLabel = Label(root1, text="年龄：", font=("微软雅黑 -20"))
    AgeLabel.place(x=30, y=260, height=40, width=80)

    #创建文本框组件，同时设置关联的变量
    IdEntry = Entry(root1, textvariable=IdVar)
    IdEntry.place(x=120, y=60, height=40, width=200)
    NameEntry = Entry(root1, textvariable=NameVar)
    NameEntry.place(x=120, y=110, height=40, width=200)
    GenderEntry = Entry(root1, textvariable=GenderVar)
    GenderEntry.place(x=120, y=160, height=40, width=200)
    TelEntry = Entry(root1, textvariable=TelVar)
    TelEntry.place(x=120, y=210, height=40, width=200)
    AgeEntry = Entry(root1, textvariable=AgeVar)
    AgeEntry.place(x=120, y=260, height=40, width=200)

    #录入学生信息
    def OkInsert():
        i = 0
        con, cur = LinkMysql()  #连接数据库，获取游标对象
        IdStu = eval(IdEntry.get())  #学号输入
        NameStu = str(NameEntry.get())  #姓名输入
        GenderStu = str(GenderEntry.get())  #姓名录入
        TelStu = eval(TelEntry.get())  #电话录入
        AgeStu = eval(AgeEntry.get())  #年龄录入
        # 查找完成若有重复的学号，则警告。否则录入数据库
        num = cur.execute("SELECT * from student where StuId =  " + str(IdStu) + " ;")
        if num > 0:  #如果有记录
            i = 1  #赋值为1
        else:  #如果没有记录
            i = 0  #赋值为0
        if i == 1:  #如果有记录，弹窗提醒
            messagebox.showerror("警告", message="学号重复，请重新输入！！！")
        else:  #如果没有记录
            try:  #插入语句
                sql1 = "INSERT INTO student(StuId,Name,Gender,Tel,Age)"
                sql1 += "VALUES(%d,'%s','%s',%d,%d)" % (IdStu, NameStu, GenderStu, TelStu, AgeStu)
                cur.execute(sql1)  #执行插入语句
                con.commit()  #提交事务执行
                messagebox.showinfo(title="恭喜", message=NameStu + "信息录入成功！")
                root1.destroy()  #退出该窗口
            except:
                messagebox.showerror("警告", message=NameStu + "信息录入错误！")

    #录入学生信息按钮+绑定功能函数
    InsertButton = Button(root1, text="录入学生信息", font=("微软雅黑 -20"), bg="gray", command=OkInsert)
    InsertButton.place(x=150, y=350, height=40, width=200)

    #取消键功能,信息清空
    def Cancel():
        IdVar.set("")
        NameVar.set("")
        GenderVar.set("")
        TelVar.set("")
        AgeVar.set("")

    #取消键
    CancelButton = Button(root1, text="取消", font=("微软雅黑 -20"), bg="gray", command=Cancel)
    CancelButton.place(x=150, y=400, height=40, width=200)
    #退出键
    DeleteButton = Button(root1, text="退出", font=("微软雅黑 -20"), bg="gray", command=root1.destroy)
    DeleteButton.place(x=150, y=450, height=40, width=200)
    root1.mainloop()


# 实现删除学生信息(学号)
def DeleteStu():
    root2 = Tk()
    root2.title("删除学生信息")
    root2.geometry("600x600")  #大小
    #创建标签
    IdLabel = Label(root2, text="学号：", font=("微软雅黑 -20"))
    IdLabel.place(x=100, y=100, height=40, width=80)
    IdEntry = Entry(root2)  #学号输入框
    IdEntry.place(x=180, y=100, height=40, width=200)

    def OkDelete():  #删除学生信息
        con, cur = LinkMysql()  #连接数据库，获取游标对象
        IdStu = eval(IdEntry.get())  #学号输入，根据学号删除
        #先查询是否有该学生信息
        num = cur.execute("SELECT * from student where StuId = '%s';" % IdStu)
        if num:  #如果有再删除
            cur.execute("DELETE from student where StuId = '%s';" % IdStu)
            con.commit()  #提交事务
            messagebox.showinfo(title="恭喜", message="删除成功！")
            root2.destroy()  #删除之后就退出
        else:  #如果没有弹窗提示
            messagebox.showerror("警告", message="不存在该学生信息，请先录入！")
            root2.destroy()  #删除之后就退出

    #删除键
    DeleteButton1 = Button(root2, text="删除", font=("微软雅黑 -20"), bg="gray", command=OkDelete)
    DeleteButton1.place(x=180, y=220, height=40, width=200)

    #退出键
    DeleteButton2 = Button(root2, text="退出", font=("微软雅黑 -20"), bg="gray", command=root2.destroy)
    DeleteButton2.place(x=180, y=300, height=40, width=200)
    root2.mainloop()


# 查询学生信息
def SelectStu():
    root3 = Tk()
    root3.title("查询学生信息")
    root3.geometry("600x600")  #大小
    #创建关联变量
    SelectIdVar = StringVar(root3, value='')
    #创建文本组件框\标签组件
    SelectIdLabel = Label(root3, text="学号", font=("微软雅黑 -20"))
    SelectIdLabel.place(x=130, y=10, height=40, width=80)
    SelectIdEntry = Entry(root3, textvariable=SelectIdVar)
    SelectIdEntry.place(x=220, y=10, height=40, width=200)

    def OkSelect():
        #创建关联字符变量
        IdVar = StringVar(root3, value="")  #学号
        NameVar = StringVar(root3, value="")  #姓名
        GenderVar = StringVar(root3, value="")  #性别
        TelVar = StringVar(root3, value="")  #电话
        AgeVar = StringVar(root3, value="")  #年龄

        con, cur = LinkMysql()  #连接数据库，获取游标对象
        IdStu = eval(SelectIdEntry.get())  #学号输入
        #查询学号是否存在
        num = cur.execute("SELECT * from student where StuId = '%d';" % IdStu)
        con.commit()  #提交事务
        if num:
            l_row = cur.fetchall()  #查询所有记录
            for row in l_row:
                #获取各个字段信息
                if IdStu == row[0]:
                    NameStu = row[1]
                    GenderStu = row[2]
                    TelStu = row[3]
                    AgeStu = row[4]
            #设置成字符串变量的值      
            IdVar.set(IdStu)
            NameVar.set(NameStu)
            GenderVar.set(GenderStu)
            TelVar.set(TelStu)
            AgeVar.set(AgeStu)
        else:  #如果没有信息
            messagebox.showinfo(title="警告", message="无当前学生信息！！！")

        #创建标签组件
        IdLabel = Label(root3, text="学号：", font=("微软雅黑 -20"))
        IdLabel.place(x=130, y=160, height=40, width=80)
        NameLabel = Label(root3, text="姓名：", font=("微软雅黑 -20"))
        NameLabel.place(x=130, y=210, height=40, width=80)
        GenderLabel = Label(root3, text="性别：", font=("微软雅黑 -20"))
        GenderLabel.place(x=130, y=260, height=40, width=80)
        TelLabel = Label(root3, text="电话：", font=("微软雅黑 -20"))
        TelLabel.place(x=130, y=310, height=40, width=80)
        AgeLabel = Label(root3, text="年龄：", font=("微软雅黑 -20"))
        AgeLabel.place(x=130, y=360, height=40, width=80)

        #创建文本框组件，同时设置关联的变量
        IdEntry = Entry(root3, textvariable=IdVar)
        IdEntry.place(x=220, y=160, height=40, width=200)
        NameEntry = Entry(root3, textvariable=NameVar)
        NameEntry.place(x=220, y=210, height=40, width=200)
        GenderEntry = Entry(root3, textvariable=GenderVar)
        GenderEntry.place(x=220, y=260, height=40, width=200)
        TelEntry = Entry(root3, textvariable=TelVar)
        TelEntry.place(x=220, y=310, height=40, width=200)
        AgeEntry = Entry(root3, textvariable=AgeVar)
        AgeEntry.place(x=220, y=360, height=40, width=200)

    #查询键
    SelectButton = Button(root3, text="查询", font=("微软雅黑 -20"), bg="gray", command=OkSelect)
    SelectButton.place(x=250, y=80, height=40, width=100)

    #取消键
    def Cancel():
        SelectIdVar.set('')

    CancelButton = Button(root3, text="取消", font="微软雅黑 -20", bg="gray", command=Cancel)
    CancelButton.place(x=100, y=80, height=40, width=100)
    #退出键
    DeleteButton = Button(root3, text="退出", font="微软雅黑 -20", bg="gray", command=root3.destroy)
    DeleteButton.place(x=400, y=80, height=40, width=100)
    root3.mainloop()


#修改学生信息
def ChangeStu():
    root4 = Tk()
    root4.title("修改学生信息")
    root4.geometry("600x600")  #大小
    #创建关联变量
    SelectIdVar = StringVar(root4, value='')
    #创建文本组件框\标签组件
    SelectIdLabel = Label(root4, text="学号", font=("微软雅黑 -20"))
    SelectIdLabel.place(x=130, y=10, height=40, width=80)
    SelectIdEntry = Entry(root4, textvariable=SelectIdVar)
    SelectIdEntry.place(x=220, y=10, height=40, width=200)

    def OkSelect():
        #创建关联字符变量
        IdVar = StringVar(root4, value="")  #学号
        NameVar = StringVar(root4, value="")  #姓名
        GenderVar = StringVar(root4, value="")  #性别
        TelVar = StringVar(root4, value="")  #电话
        AgeVar = StringVar(root4, value="")  #年龄

        con, cur = LinkMysql()  #连接数据库，获取游标对象
        IdStu = eval(SelectIdEntry.get())  #学号输入
        #查询学号是否存在
        num = cur.execute("SELECT * from student where StuId = '%d';" % IdStu)
        con.commit()  #提交事务
        if num:
            l_row = cur.fetchall()  #查询所有记录
            for row in l_row:
                #获取各个字段信息
                if IdStu == row[0]:
                    NameStu = row[1]
                    GenderStu = row[2]
                    TelStu = row[3]
                    AgeStu = row[4]
            #设置成字符串变量的值      
            IdVar.set(IdStu)
            NameVar.set(NameStu)
            GenderVar.set(GenderStu)
            TelVar.set(TelStu)
            AgeVar.set(AgeStu)
        else:  #如果没有信息
            messagebox.showinfo(title="警告", message="无当前学生信息！！！")

        #创建标签组件
        IdLabel = Label(root4, text="学号：", font=("微软雅黑 -20"))
        IdLabel.place(x=130, y=160, height=40, width=80)
        NameLabel = Label(root4, text="姓名：", font=("微软雅黑 -20"))
        NameLabel.place(x=130, y=210, height=40, width=80)
        GenderLabel = Label(root4, text="性别：", font=("微软雅黑 -20"))
        GenderLabel.place(x=130, y=260, height=40, width=80)
        TelLabel = Label(root4, text="电话：", font=("微软雅黑 -20"))
        TelLabel.place(x=130, y=310, height=40, width=80)
        AgeLabel = Label(root4, text="年龄：", font=("微软雅黑 -20"))
        AgeLabel.place(x=130, y=360, height=40, width=80)

        #创建文本框组件，同时设置关联的变量
        IdEntry = Entry(root4, textvariable=IdVar)
        IdEntry.place(x=220, y=160, height=40, width=200)
        NameEntry = Entry(root4, textvariable=NameVar)
        NameEntry.place(x=220, y=210, height=40, width=200)
        GenderEntry = Entry(root4, textvariable=GenderVar)
        GenderEntry.place(x=220, y=260, height=40, width=200)
        TelEntry = Entry(root4, textvariable=TelVar)
        TelEntry.place(x=220, y=310, height=40, width=200)
        AgeEntry = Entry(root4, textvariable=AgeVar)
        AgeEntry.place(x=220, y=360, height=40, width=200)

        #添加保存功能
        def SaveName():  #保存姓名
            con, cur = LinkMysql()  #连接数据库，获取游标对象
            Name = NameEntry.get()  #获取输入框内容
            UpdateSql = "UPDATE student SET Name='%s' WHERE StuId=%d;" % (Name, eval(IdEntry.get()))
            cur.execute(UpdateSql)
            con.commit()
            messagebox.showinfo(title='恭喜', message='姓名修改成功！！！')

        def SaveGender():  #保存性别
            con, cur = LinkMysql()  #连接数据库，获取游标对象
            Gender = GenderEntry.get()  #获取输入框内容
            UpdateSql = "UPDATE student SET Gender='%s' WHERE StuId=%d;" % (Gender, eval(IdEntry.get()))
            cur.execute(UpdateSql)
            con.commit()
            messagebox.showinfo(title='恭喜', message='性别修改成功！！！')

        def SaveTel():  #保存电话
            con, cur = LinkMysql()  #连接数据库，获取游标对象
            Tel = eval(TelEntry.get())  #获取输入框内容
            UpdateSql = "UPDATE student SET Tel=%d WHERE StuId=%d;" % (Tel, eval(IdEntry.get()))
            cur.execute(UpdateSql)
            con.commit()
            messagebox.showinfo(title='恭喜', message='电话修改成功！！！')

        def SaveAge():  #保存年龄
            con, cur = LinkMysql()  #连接数据库，获取游标对象
            Age = eval(AgeEntry.get())  #获取输入框内容
            UpdateSql = "UPDATE student SET Age=%d WHERE StuId=%d;" % (Age, eval(IdEntry.get()))
            cur.execute(UpdateSql)
            con.commit()
            messagebox.showinfo(title='恭喜', message='年龄修改成功！！！')

        #保存键
        SaveNameButton = Button(root4, text="保存", font=("微软雅黑 -20"), bg="gray", command=SaveName)
        SaveNameButton.place(x=450, y=210, height=40, width=60)
        SaveGenderButton = Button(root4, text="保存", font=("微软雅黑 -20"), bg="gray", command=SaveGender)
        SaveGenderButton.place(x=450, y=260, height=40, width=60)
        SaveTelButton = Button(root4, text="保存", font=("微软雅黑 -20"), bg="gray", command=SaveTel)
        SaveTelButton.place(x=450, y=310, height=40, width=60)
        SaveAgeButton = Button(root4, text="保存", font=("微软雅黑 -20"), bg="gray", command=SaveAge)
        SaveAgeButton.place(x=450, y=360, height=40, width=60)

    #查询键
    SelectButton = Button(root4, text="查询", font=("微软雅黑 -20"), bg="gray", command=OkSelect)
    SelectButton.place(x=250, y=80, height=40, width=100)

    #取消键
    def Cancel():
        SelectIdVar.set('')

    CancelButton = Button(root4, text="取消", font="微软雅黑 -20", bg="gray", command=Cancel)
    CancelButton.place(x=100, y=80, height=40, width=100)
    #退出键
    DeleteButton = Button(root4, text="退出", font="微软雅黑 -20", bg="gray", command=root4.destroy)
    DeleteButton.place(x=400, y=80, height=40, width=100)

    root4.mainloop()


def Main():
    Windows()  #添加窗口
    menu()  #调用函数添加菜单栏
    MainButton()  #调用函数添加主页按钮


Main()  #执行主函数
